Hexだけで中森明菜の名曲「サザン・ウインド」についてSpotifyからデータを取得してSnowflakeにロードして分析する

Hexだけで中森明菜の名曲「サザン・ウインド」についてSpotifyからデータを取得してSnowflakeにロードして分析する

作曲は玉置浩二ですが、本人によるセルフカバーも最高です。
Clock Icon2023.01.26

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

白いヨットの上の美少年の大阪オフィスの玉井です。

久しぶりの中森明菜シリーズとなります。

題名の通り、Hexを使って、Spotifyから中森明菜の名曲「サザン・ウインド」のデータの取得〜DWH(Snowflake)へロード〜分析を一気通貫でやりたいと思います。Hex以外では作業・処理はしないところがポイントです。

Hexとは?

こちらをどうぞ。

中森明菜の名曲「サザン・ウインド」とは?

本記事でやることの概要

Hexの特徴の一つは同一NotebookでSQLとPythonを両方使えることです。それを最大限に活かす方向で、今回のプロジェクトを実施したいと思います。

  • PythonでSpotifyからサザン・ウインドのデータを取得する
  • Python+SQLでサザン・ウインドのデータをSnowflakeにロードする
  • SQLでサザン・ウインドのデータを分析する

これらの処理を、同一のNotebookで行います。

また、ロードする際は、pandas等で整形してからロード…というようなことはせず、とりあえずjsonファイルのままロードします。これはSnowflakeは半構造化データを扱うことができるため、とりあえず先に入れて後からSQLで整形する方が楽だからです(つまりETLではなくELTで行う)。

実施環境

本記事は、以下の状態を前提にしています。

  • Hexが使用できる
  • Snowflakeが使用できる
  • HexとSnowflakeが連携している
  • Spotify APIが利用できる(クライアントシークレットとかが発行できる)

やってみた

Spotifyからデータ取得編

各種Secretを設定する

Hexでは、パスワード等のベタ書き厳禁なパラメータをSecretとして別で設定することができます(まあ他のツールでも大体できると思いますが)。今回は個人でやるだけですので、別に設定しなくても問題ないっちゃないのですが、せっかくなので設定します。

Variablesというメニューから、Secret登録メニューにいきます。

後は、ただひたすらに、名前と値を設定するだけです。

今回は以下を設定しました。

  • Spotify APIの…
    • クライアントID
    • クライアントシークレット
  • Snowflakeの…
    • アカウント名
    • ユーザー名
    • パスワード

Spotifyからデータを取得する

まずはSpotifyからサザン・ウインドのデータを取得します。

SpotifyにはSpotipyというPython用のライブラリがあるため、これを使用します。Hexにデフォルトで存在しないため、インストールから行います(Notebook系のサービス?ではエクスクラメーションマークをつけることでpipが実行できるってことを今回の件で初めて知りました)。

!pip install spotipy --upgrade

こんな感じでインストールされます。

後は、データを取得するだけです。

ちなみに、サザン・ウインドの何のデータをとるかって話ですが、前回と同じく「Get Audio Analysis for a Track」で取得できる楽曲の詳細なデータをとります。

import json
import spotipy
import snowflake.connector as sc
from spotipy.oauth2 import SpotifyClientCredentials

client_credentials_manager = spotipy.oauth2.SpotifyClientCredentials(sp_client_id, sp_client_secret)
spotify = spotipy.Spotify(client_credentials_manager=client_credentials_manager)
# サザン・ウインドのSpotify ID
track_id = 'https://open.spotify.com/track/2dZHD66ameYxyZkJtR6bYG?si=8629cbd4675e417f'

# Spotify APIを叩く
audio_analysis = spotify.audio_analysis(track_id)

snowflake.connectorですが、これは後で使うため、ここで一緒にインポートしています。

データをHexに置く

この段階では、上記で取得したデータは辞書型として保持しています。これは後々Snowflakeにロードするため、ファイルとして一旦どこかに置いておく必要があります。

Hexの場合、下記のようにファイルとして吐き出すことで、Hex上にファイルを置くことができます。「Hexから見たローカル」って感じでしょうか。

path = './southern_wind_analysis.json'
audio_file = open(path, mode="w")

json.dump(audio_analysis, audio_file)

置いたファイルは、左メニューのFilesから確認できます。

Snowflakeへロード編

テーブルを作成する

サザン・ウインドのデータを入れるためのテーブルを先に作っておきます。冒頭で書いたとおり、今回はjsonファイルをそのままぶちこむため、VARIANT型のカラムを1つ用意するだけとなります。

CREATE TABLE "TAMAI_TEST_DB"."SPOTIFY"."SOUTHERN_WIND"(
    "RAW_STATUS" VARIANT
)

急にコードがPythonからSQLになったため、「ん、ここでSnowflakeの画面にうつったのかな?」と思われるかもしれませんが、Hexから一切動いておりません。同じ画面で処理を行っています。

Spotifyデータ(ファイル)をステージングする

次は、テーブルにロードしたいデータをSnowflakeのステージに配置します。

今回はテーブルステージを使用します(楽なので)。先ほど作ったテーブルのテーブルステージに、ファイルをステージングします。

conn = sc.connect(
    user=username,
    password=password,
    account=account_name,
    role='SYSADMIN',
    warehouse='X_SMALL_WH',
    database='TAMAI_TEST_DB',
    schema ='SPOTIFY'
)

conn.cursor().execute("PUT file:///hex/southern_wind_analysis.json @%SOUTHERN_WIND")

これを見たとき「え、またPythonに戻ってる?」「っていうかSnowflakeはステージングもSQLでできるんだから、HexだったらSQLで出来るのでは?」とか言いたくなると思います。

私も同じことを思い、最初はSQLでPUTしてみたのですが、うまくステージングできませんでした(エラーもないがステージングもされてない)。おそらく、Hex上でネイティブで実行できるSQLだと、Hexに配置しているファイルが認識できないのだと思われます。

ですので、ファイルのエクスポート&配置が出来たPythonで実行することにしました(このためにsnowflake.connectorをインポートしていた)。

テーブルにデータをロードする

ファイルをステージングできたら、後はそこからテーブルにロードするだけです(言い忘れていましたが、ファイルフォーマットは作成済でした)。

COPY INTO "TAMAI_TEST_DB"."SPOTIFY"."SOUTHERN_WIND"
FILE_FORMAT = (type = json);

ロードできているかどうか、Snowflake側で確認します。

jsonファイルの中身がそのままテーブルにロードされていますね。

分析編(おまけレベル)

データを構造化の形にする

無事、テーブルにロードすることはできましたが、この半構造化の状態では分析することはほぼ不可能です。というわけで、これを構造化の形にします。

…面倒だったので、前回と全く同じクエリを使い回すことにします。許してくれ。

WITH tmp_wind AS(
    SELECT
        VALUE AS raw_status
    FROM
         "TAMAI_TEST_DB"."SPOTIFY"."SOUTHERN_WIND"
        ,LATERAL FLATTEN(
            input => RAW_STATUS:sections
        )
)
SELECT
     raw_status:start::float AS "start"
    ,raw_status:duration::float AS "duration"
    ,raw_status:confidence::float AS "confidence"
    ,raw_status:loudness::float AS "loudness"
    ,raw_status:tempo::float AS "tempo"
    ,raw_status:tempo_confidence::float AS "tempo_confidence"
    ,raw_status:key::integer::float AS "key"
    ,raw_status:key_confidence::float AS "key_confidence"
    ,raw_status:mode::integer AS "mode"
    ,raw_status:mode_confidence::float AS "mode_confidence"
    ,raw_status:time_signature::integer AS "time_signature"
    ,raw_status:time_signature_confidence::float AS "time_signature_confidence"
FROM
    tmp_wind

Hex上でも、曲のセクション毎のデータがキレイに取得できました。

可視化する

Hexはグラフを作ることもできます。しかも、前セルで実行したSELECT文の結果を自動でdataframeとして持ってくれるため、設定が簡単です。

というわけで、セクション毎の音量(loudness)の大きさを折れ線グラフにしてみました。

最後がガッて下がってますが、サザン・ウインドを聴き込んでいる皆さんなら「あ〜〜」ってなりますよね。この曲のアウトロは「ティンティンティンティン…」って感じで、急にシンセサイザーか何かの音だけが残りつつ終わるので、こういうグラフになるんですねえ。

完成形

今回のHexプロジェクトは、Githubの自分のリポジトリと連携しています(Git Syncという機能)。ズラーッと見たい場合はこちらをどうぞ。

…といっても、これはHexファイル形式(yaml)というものらしく、他のNotebook等で確認することはできません(その代わり、Hexだとインポートしたときに完璧に再現できる)。一応、手動では.ipynbで出すこともできます。

メモなど

ロードの方法

今回はHexローカル(?)にファイルを出してからロードしましたが、当初はS3やGCS等のクラウドストレージに配置→Snowflakeにロード、という方法を考えていました(Snowflake的に言うと「外部ステージ」を使う)。

これも出来るっちゃ出来るのですが、外部ステージの設定として、Snowflakeとクラウドストレージを連携させる必要があり、それってもうHex以外でもゴリゴリ作業しちゃってる感が出たため、今回は辞めました。

ただ、Hex上からクラウドストレージにファイルをアップロードすること自体はできるので(GCSで確認済)、別の使い方をするときは、この方法も便利だと思います。

おわりに

中森明菜の「サザン・ウインド」は名曲ということがわかりましたね。

今度はDagsterとかdbtとか使って似たようことをやってみたいですね。

参考にしたドキュメントなど

この記事をシェアする

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.